const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Score = sequelize.define('Score', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  student_id: {
    type: DataTypes.STRING(20),
    allowNull: false,
    field: 'student_id',
    references: {
      model: 'students',
      key: 'student_id'
    },
    comment: '准考证号'
  },
  exam_date: {
    type: DataTypes.DATEONLY,
    allowNull: false,
    field: 'exam_date',
    comment: '考试日期'
  },
  exam_name: {
    type: DataTypes.STRING(100),
    allowNull: false,
    field: 'exam_name',
    comment: '考试名称'
  },
  // 各科成绩
  chinese: {
    type: DataTypes.DECIMAL(5, 2),
    allowNull: true,
    comment: '语文'
  },
  math: {
    type: DataTypes.DECIMAL(5, 2),
    allowNull: true,
    comment: '数学'
  },
  english: {
    type: DataTypes.DECIMAL(5, 2),
    allowNull: true,
    comment: '英语'
  },
  physics: {
    type: DataTypes.DECIMAL(5, 2),
    allowNull: true,
    comment: '物理'
  },
  chemistry: {
    type: DataTypes.DECIMAL(5, 2),
    allowNull: true,
    comment: '化学'
  },
  biology: {
    type: DataTypes.DECIMAL(5, 2),
    allowNull: true,
    comment: '生物'
  },
  politics: {
    type: DataTypes.DECIMAL(5, 2),
    allowNull: true,
    comment: '政治'
  },
  history: {
    type: DataTypes.DECIMAL(5, 2),
    allowNull: true,
    comment: '历史'
  },
  geography: {
    type: DataTypes.DECIMAL(5, 2),
    allowNull: true,
    comment: '地理'
  },
  total_score: {
    type: DataTypes.DECIMAL(6, 2),
    allowNull: true,
    field: 'total_score',
    comment: '总分'
  },
  school_rank: {
    type: DataTypes.INTEGER,
    allowNull: true,
    field: 'school_rank',
    comment: '校次'
  }
}, {
  tableName: 'scores',
  timestamps: true,
  createdAt: 'created_at',
  updatedAt: 'updated_at',
  underscored: true,
  indexes: [
    {
      name: 'idx_student_id',
      fields: ['student_id']
    },
    {
      name: 'idx_exam_date',
      fields: ['exam_date']
    },
    {
      name: 'idx_school_rank',
      fields: ['school_rank']
    }
  ]
});

module.exports = Score;
